package tech.yunland.shop.domain.dao;

import com.moyuer.bsf.domain.dao.BaseDao;
import com.moyuer.bsf.domain.dao.OrganDao;
import com.moyuer.bsf.domain.dao.UserDao;
import com.moyuer.bsf.domain.model.Organ;
import com.moyuer.bsf.domain.model.User;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.ObjectUtils;
import tech.yunland.shop.domain.model.Goods;
import tech.yunland.shop.domain.model.GoodsPaid;
import tech.yunland.shop.domain.model.SaleBill;
import tech.yunland.shop.domain.model.SaleBillEntry;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 王睿智 <geekrize@gmail.com>
 */
@Repository
public class GoodsPaidDao extends BaseDao<GoodsPaid, String> {

    @Autowired
    private GoodsDao goodsDao;

    @Autowired
    private OrganDao organDao;

    @Autowired
    private UserDao userDao;

    /**
     * 计入预购商品
     *
     * @param organId
     * @param memberId
     * @param goodsId
     * @param quantity
     * @param amount
     */
    public void add(String organId, String memberId, String goodsId, BigDecimal quantity, BigDecimal amount) {
        quantity = (quantity == null ? BigDecimal.ZERO : quantity);
        amount = (amount == null ? BigDecimal.ZERO : amount);

        DetachedCriteria criteria = DetachedCriteria.forClass(GoodsPaid.class);
        criteria.createCriteria("goods").add(Restrictions.idEq(goodsId));
        criteria.createCriteria("organ").add(Restrictions.idEq(organId));
        criteria.createCriteria("member").add(Restrictions.idEq(memberId));
        List<GoodsPaid> list = this.findByCriteria(criteria);

        Organ organ = organDao.get(organId);
        User member = userDao.get(memberId);
        Goods goods = goodsDao.get(goodsId);

        GoodsPaid goodsPaid = null;
        if (list == null || list.size() == 0) {
            goodsPaid = new GoodsPaid();
            goodsPaid.setOrgan(organ);
            goodsPaid.setMember(member);
            goodsPaid.setGoods(goods);
            goodsPaid.setQuantity(quantity);
            goodsPaid.setAmount(amount);

            this.save(goodsPaid);
        } else if (list.size() == 1) {
            goodsPaid = list.get(0);
            goodsPaid.setQuantity(quantity.add(goodsPaid.getQuantity()));
            goodsPaid.setAmount(amount.add(goodsPaid.getAmount()));
            goodsPaid.setLastModify(new Date());

            this.update(goodsPaid);
        } else {
            throw new RuntimeException("预购商品数据异常");
        }
    }

    /**
     * 从预购商品中扣除
     * @param organId
     * @param memberId
     * @param goodsId
     * @param quantity
     * @param bill
     * @param entry
     */
    public BigDecimal substract(int payment, String organId, String memberId, String goodsId, BigDecimal quantity, BigDecimal received, SaleBill bill, SaleBillEntry entry) {
        quantity = (quantity == null ? BigDecimal.ZERO : quantity);

        User member = null;
        Goods goods = null;
        GoodsPaid goodsPaid = null;
        List<GoodsPaid> list = null;
        BigDecimal memberPrize = null;

        DetachedCriteria criteria = DetachedCriteria.forClass(GoodsPaid.class);
        criteria.createCriteria("organ").add(Restrictions.idEq(organId));
        criteria.createCriteria("member").add(Restrictions.idEq(memberId));

        // 扣卡
        if (payment == 10) {
            criteria.createCriteria("goods").add(Restrictions.idEq(goodsId));
            list = this.findByCriteria(criteria);

            member = userDao.get(memberId);
            goods = goodsDao.get(goodsId);
            String reminder = "会员 [" + member.getName() + "] 卡中商品[" + goods.getName() + "]数量不足,不能完成支付！";

            if (list == null || list.size() == 0) {
                throw new RuntimeException(reminder);
            }
            goodsPaid = list.get(0);
            if (goodsPaid != null) {
                if (goodsPaid.getQuantity().compareTo(BigDecimal.ZERO) == 0) {// 当没有次数的时候
                    throw new RuntimeException(reminder);

                }
                memberPrize = goodsPaid.getAmount().divide(goodsPaid.getQuantity(), 10, BigDecimal.ROUND_HALF_UP);
            }

            if (goodsPaid != null && goodsPaid.getQuantity().compareTo(quantity) >= 0) {
                goodsPaid.setQuantity(goodsPaid.getQuantity().subtract(quantity));
                BigDecimal multiply = memberPrize.multiply(quantity);
                goodsPaid.setAmount(goodsPaid.getAmount().subtract(multiply));
                goodsPaid.setLastModify(new Date());
                this.update(goodsPaid);
                return multiply;
            } else {
                throw new RuntimeException(reminder);
            }

            // 现金 微信 支付宝 银行卡
        } else if (payment == 20 || payment == 60 || payment == 70 || payment == 80) {

            // TODO

            // 积分
        } else if (payment == 30) {
            criteria.createCriteria("goods").add(Restrictions.idEq(Goods.JF_ID));
            list = this.findByCriteria(criteria);

            member = userDao.get(memberId);
            goods = goodsDao.get(goodsId);
            if (goods.getScore() == null){
                throw new RuntimeException("该商品不支持积分结算");
            }
            BigDecimal score = goods.getScore().multiply(quantity);// 积分换算
            String reminder = "会员 [" + member.getName() + "] 卡中积分数量不足,不能完成支付！";

            if (list == null || list.size() == 0) {
                throw new RuntimeException(reminder);
            }
            goodsPaid = list.get(0);
            if (goodsPaid != null && goodsPaid.getQuantity().compareTo(score) >= 0) {
                goodsPaid.setQuantity(goodsPaid.getQuantity().subtract(score));
                goodsPaid.setAmount(BigDecimal.ZERO);
                goodsPaid.setLastModify(new Date());
                this.update(goodsPaid);
                return BigDecimal.ZERO;
            } else {
                throw new RuntimeException(reminder);
            }

            // 代金券
        } else if (payment == 40) {
            criteria.createCriteria("goods").add(Restrictions.idEq(Goods.DJQ_ID));
            list = this.findByCriteria(criteria);

            member = userDao.get(memberId);
//            goods = goodsDao.get(goodsId);
            goods = goodsDao.get(Goods.DJQ_ID);

            // BigDecimal cost =
            // goods.getMemberPrize().multiply(quantity);//代金券实际成本合计

            String reminder = "会员 [" + member.getName() + "] 卡中代金券余额不足,不能完成支付！";

            if (list == null || list.size() == 0) {
                throw new RuntimeException(reminder);
            }
            goodsPaid = list.get(0);
//            if (goodsPaid != null && goodsPaid.getQuantity().compareTo(entry.getAmount()) >= 0) {
//                BigDecimal cost = list.get(0).getAmount().divide(list.get(0).getQuantity(), 2,
//                        BigDecimal.ROUND_HALF_UP);
//                goodsPaid.setQuantity(goodsPaid.getQuantity().subtract(entry.getAmount()));
//
//                BigDecimal multiply = cost.multiply(entry.getAmount());
//                goodsPaid.setAmount(goodsPaid.getAmount().subtract(multiply));
//                goodsPaid.setLastModify(new Date());
//                this.update(goodsPaid);
//                return multiply;
//            } else {
//                throw new RuntimeException(reminder);
//            }
            if (goodsPaid != null){
                BigDecimal _quantity = goodsPaid.getQuantity();//已购代金券剩余数量
                BigDecimal _price = goods.getPrice();//代金券实际单价
                Goods _goods = goodsDao.get(goodsId);//该商品
                BigDecimal amount = _goods.getPrice().multiply(quantity);
                if (_quantity.multiply(_price).compareTo(amount) >= 0){
                    BigDecimal cost = goodsPaid.getAmount().divide(_quantity, 2,
                        BigDecimal.ROUND_HALF_UP);// 该会员已购代金券的单价
                    BigDecimal subtract = _quantity.subtract(amount.divide(_price, 2, BigDecimal.ROUND_HALF_UP));
                    goodsPaid.setQuantity(subtract);//剩余代金券
                    goodsPaid.setAmount(subtract.multiply(cost));
                    goodsPaid.setLastModify(new Date());
                    this.update(goodsPaid);
                    return entry.getAmount();
                }else {
                    throw new RuntimeException(reminder);
                }
            }else {
                throw new RuntimeException(reminder);
            }
            // 暂存
        } else if (payment == 0) {
            // 不计算
        }
        else if (payment == 50){ // 赊帐
            //不计算
        }
        else {
            throw new RuntimeException("支付方式错误");
        }
        return BigDecimal.ZERO;
    }

    /**
     * 判断预购表中的商品是否能够删除
     *
     * @param organId
     * @param goodsId
     * @return Boolean
     */
    public String useGoods(String organId, String goodsId) {
        String state = "";
        DetachedCriteria criteria = DetachedCriteria.forClass(GoodsPaid.class);
        criteria.createCriteria("goods").add(Restrictions.idEq(goodsId));
        criteria.createCriteria("organ").add(Restrictions.idEq(organId));
        List<GoodsPaid> list = this.findByCriteria(criteria);
        if (list == null || list.size() == 0) {
            state = "true";
        } else if (list != null || list.size() > 0) {
            for (GoodsPaid goodsPaid : list) {
                if (goodsPaid.getQuantity().equals(BigDecimal.ZERO)) {
                    state = "true";
                } else {
                    state = "false";
                }
            }
        }
        return state;
    }


    /**
     * 会员扣卡详情
     *
     * @param organId
     * @param userId
     * @param page
     * @param row
     * @param returnType
     * @return
     */
    public List<Map<String, Object>> getReservedGoods(String organId, String userId, Integer page, Integer row,
                                                      String returnType) {

        StringBuffer buffer = new StringBuffer();

        buffer.append("   select record.sn as sn,record.bizdate as bizdate,record.organ_id as organ_id,record.goods_id as goods_id,   ");
        buffer.append("   record.name as goods_name,record.member_id as member_id,bu.name as member_name, ");
        buffer.append("   record.quantity as quantity,record.amount as amount, record.amount/ (case when record.quantity=0 then 1 else record.quantity end  ) as price, record.receivable,record.received,");
        buffer.append("   (case record.payment  when '10' then '刷卡' when '20' then '现金'  when '30' then '积分' when '40' then '代金券' else '无' end) as payment, ");
        buffer.append("   (case record.type  when 'sale' then '消费' when 'charge' then '充值' else '无' end) as type from  ");
        buffer.append("   (select bs.sn as sn,to_char(bs.bizdate,'yyyy-mm-dd hh24:mi:ss') as bizdate,bs.member_id as member_id, ");
        buffer.append("   bs.organ_id as organ_id,g.name as name,bse.quantity as quantity, g.price*bse.quantity as receivable,bse.amount as received,");
        buffer.append("   bse.price*bse.quantity as amount,bse.payment as payment,'sale' as type,g.id as goods_id  from bill_sale bs  ");
        buffer.append("   left join bill_sale_entry bse on bs.id=bse.owner_id ");
        buffer.append("   left join goods g on g.id=bse.goods_id union all ");
        buffer.append("   select bc.sn as sn,to_char(bc.bizdate,'yyyy-mm-dd hh24:mi:ss') as bizdate,bc.member_id as member_id, ");
        buffer.append("   bc.organ_id as organ_id,gp.name as name,bce.quantity as quantity,bc.receivable,bc.received,bce.amount as amount, ");
        buffer.append("   '20' as payment,'charge' as type,gp.id as goods_id from bill_charge bc  ");
        buffer.append("   left join bill_charge_entry bce on bc.id=bce.owner_id ");
        buffer.append("   left join goods_package gp on gp.id=bce.goodspackage_id) record ");
        buffer.append("   left join bsf_users bu on bu.id=record.member_id ");
        buffer.append("   where record.member_id is not null and record.organ_id='" + organId + "' and record.member_id='" + userId + "' and record.quantity is not null");
        buffer.append("  order by record.bizdate desc ");

        if (returnType.equals("details")) {
            return this.findBySQL(buffer.toString(), page, row);
        }
        return this.findBySQL(buffer.toString());
    }

    /**
     * 获取会员已购商品
     *
     * @param goodsId
     * @param organId
     * @param memberId
     * @return
     */
    public GoodsPaid getMemberPrize(String goodsId, String organId, String memberId) {

        DetachedCriteria criteria = DetachedCriteria.forClass(GoodsPaid.class);
        criteria.createCriteria("goods").add(Restrictions.idEq(goodsId));
        criteria.createCriteria("organ").add(Restrictions.idEq(organId));
        criteria.createCriteria("member").add(Restrictions.idEq(memberId));
        List<GoodsPaid> list = this.findByCriteria(criteria);

        return list.get(0);

    }

    /**
     * 获取会员已购商品
     *
     * @param goodsId
     * @param organId
     * @param memberId
     * @return
     */
    public GoodsPaid getMemberPrizeBySql(String goodsId, String organId, String memberId) {

        String sql = "select quantity,amount from goods_paid where goods_id = ? and organ_id = ? and member_id = ?";
        Map<String, Object> result = this.findUniqueBySQL(sql, new Object[]{goodsId, organId, memberId});
        if (result == null) {
            return null;
        }
        GoodsPaid gp = new GoodsPaid();
        Object _quantity = result.get("quantity");
        Object _amount = result.get("amount");
        if (!ObjectUtils.isEmpty(_quantity)) {
            gp.setQuantity(new BigDecimal(_quantity.toString()));
        }
        if (!ObjectUtils.isEmpty(_amount)) {
            gp.setAmount(new BigDecimal(_amount.toString()));
        }

        return gp;

    }

    /**
     * 按会员查询剩余总额
     *
     * @param organId
     * @param page
     * @param row
     * @param returnType
     * @return
     */
    public List<Map<String, Object>> getMemberReserved(String organId, Integer page, Integer row, String returnType) {

        String sql = " select gp.member_id as member_id,bu.name as member_name,ba.file_name as picture,bu.remark as remark,sum(gp.amount) as amount,sum(gp.quantity*goods.price) AS real_price "
                + " from goods_paid gp left join bsf_users bu on bu.id=gp.member_id left join bsf_attachment ba on ba.id=bu.portrait_id LEFT JOIN goods ON gp.goods_id = goods.id "
                + " where gp.organ_id='" + organId + "' group by gp.member_id,bu.name,ba.file_name,bu.remark ";

        if (returnType.equals("details")) {
            return this.findBySQL(sql, page, row);
        }
        return this.findBySQL(sql);

    }

    /**
     * 按会员模糊查询剩余总额
     *
     * @param organId
     * @param page
     * @param row
     * @param returnType
     * @return
     */
    public List<Map<String, Object>> getMemberReservedLike(String organId, String param, Integer page, Integer row, String returnType) {

        String sql = " select gp.member_id as member_id,bu.name as member_name,ba.file_name as picture,bu.remark as remark,sum(gp.amount) as amount, sum(gp.quantity*goods.price) AS real_price"
                + " from goods_paid gp left join bsf_users bu on bu.id=gp.member_id left join bsf_attachment ba on ba.id=bu.portrait_id LEFT JOIN goods ON gp.goods_id = goods.id"
                + " where bu.spell like '" + '%' + param + '%' + "' and gp.organ_id='" + organId + "' or bu.name like '" + '%' + param + '%' + "' and gp.organ_id='" + organId + "' group by gp.member_id,bu.name,ba.file_name,bu.remark ";

        if (returnType.equals("details")) {
            return this.findBySQL(sql, page, row);
        }
        return this.findBySQL(sql);

    }


    /**
     * 查询热门商品购买力信息
     *
     * @param dateType
     * @param bizDate
     * @return
     */
    public List<Map<String, Object>> getTopInfo(String dateType, String bizDate, String organId, Integer page, Integer rows, String returnType) {

        StringBuffer buffer = new StringBuffer();

        //组织sql语句
        buffer.append(" select top.goods_id,top.name,top.bizdate, sum(top.quantity) as quantity, top.type,top.organ_id");
        buffer.append(" from ( select sale.bizdate,g.id as goods_id,g.name,saleEntry.quantity,'销售商品' as type,sale.organ_id");
        buffer.append(" from bill_sale sale");
        buffer.append(" left join bill_sale_entry  saleEntry on sale.id=saleEntry.owner_id");
        buffer.append(" left join goods g on g.id= saleEntry.goods_id");
        buffer.append(" where sale.state=1 and saleEntry.state=1");
        buffer.append(" union all");
        buffer.append(" select charge.bizdate ,chargeEntry.goodspackage_id as goods_id,gp.name,chargeEntry.quantity,'优惠活动' as type,charge.organ_id");
        buffer.append(" from bill_charge charge");
        buffer.append(" left join bill_charge_entry  chargeEntry on charge.id=chargeEntry.owner_id");
        buffer.append(" left join goods_package gp on gp.id=chargeEntry.goodspackage_id");
        buffer.append(" left join bsf_users bu on bu.id=charge.member_id");
        buffer.append(" where charge.state=1 )");
        buffer.append("  as top");

        //取值跨度(时间)
        if (dateType.equals("year")) {
            buffer.append(" where to_char(top.bizdate,'yyyy')='" + bizDate.substring(0, 4) + "'  ");
        } else if (dateType.equals("month")) {
            buffer.append(" where  to_char(top.bizdate,'yyyy-MM')='" + bizDate.substring(0, 7) + "'  ");
        } else if (dateType.equals("day")) {
            buffer.append(" where  to_char(top.bizdate,'yyyy-MM-dd')='" + bizDate.substring(0, 10) + "'  ");
        }
        buffer.append(" and top.organ_Id= '" + organId + "' ");
        buffer.append(" group by top.organ_id,top.goods_id,top.name,top.bizdate,top.type");
        buffer.append(" order by  sum(top.quantity)  desc, top.bizdate desc  ");

        if (returnType.equals("size")) {
            return findBySQL(buffer.toString());
        }
        return this.findBySQL(buffer.toString(), page, rows);
    }

    /**
     * 查询顾客消费信息
     *
     * @param dateType
     * @param bizDate
     * @param organId
     * @param page
     * @param rows
     * @param returnType
     * @return
     */
    public List<Map<String, Object>> getShopInfo(String dateType, String bizDate, String organId, Integer page, Integer rows, String returnType) {
        StringBuffer buffer = new StringBuffer();

        buffer.append(" select shop.sn, shop.organ_id,shop.quantity,shop.bizdate,shop.memberType, shop.username,shop.name,(case  payment  when '10' then '会员卡消费' when '20' then '现金消费'  when  '30' then '积分消费' when '40' then '代金券消费'  else '无' end) as payment ,shop.price,shop.type  from ");
        buffer.append(" (select sale.sn,sale.organ_id ,sale.bizdate, (case when member_id is not null then '会员' when member_id is null then '非会员' end) as memberType , (case when  bu.name is null then '0' when bu.name is not null then bu.name end)  as username,");
        buffer.append(" (case when g.name is not null then g.name when g.name is null then '0' end )  as name,saleEntry.quantity,saleEntry.payment,saleEntry.price ,'billsale' as type");
        buffer.append(" from bill_sale sale");
        buffer.append(" left join bill_sale_entry  saleEntry on sale.id=saleEntry.owner_id");
        buffer.append(" left join bsf_users bu on bu.id=sale.member_id");
        buffer.append(" left join goods g on g.id = saleEntry.goods_id");
        buffer.append(" union all");
        buffer.append(" select charge.sn , charge.organ_id,charge.bizdate, (case when member_id is not null then '会员' when member_id is null then '非会员' end) as memberType  ,(case when  bu.name is null then '0' when bu.name is not null then bu.name end)  as username,");
        buffer.append(" gp.name ,chargeEntry.quantity, '0' as payment,'0' as price,'billcharge' as type");
        buffer.append(" from bill_charge charge");
        buffer.append(" left join bill_charge_entry  chargeEntry on charge.id=chargeEntry.owner_id");
        buffer.append(" left join goods_package gp on gp.id=chargeEntry.goodspackage_id");
        buffer.append(" left join bsf_users bu on bu.id=charge.member_id )");
        buffer.append("  as shop");
        buffer.append(" where shop.organ_Id= '" + organId + "' ");

        //取值跨度(时间)
        if (dateType.equals("year")) {
            buffer.append(" and to_char(shop.bizdate,'yyyy')='" + bizDate.substring(0, 4) + "'  ");
        } else if (dateType.equals("month")) {
            buffer.append(" and  to_char(shop.bizdate,'yyyy-MM')='" + bizDate.substring(0, 7) + "'  ");
        } else if (dateType.equals("day")) {
            buffer.append(" and  to_char(shop.bizdate,'yyyy-MM-dd')='" + bizDate.substring(0, 10) + "'  ");
        }
        // 按时间排序
        buffer.append("order by bizdate desc ");

        if (returnType.equals("size")) {
            return findBySQL(buffer.toString());
        }
        return this.findBySQL(buffer.toString(), page, rows);
    }

    /*
     * 查询会员的消费能力
     */
    public List<Map<String, Object>> getMemberShopInfo(String dateType, String bizDate, String memberId, Integer page, Integer rows, String returnType) {
        StringBuffer buffer = new StringBuffer();

        buffer.append(" select top.id,top.organ_id,top.member_id,top.username,top.quantity,top.amount,max(top.bizdate) as lastTime from");
        buffer.append(" (select sale.id, sale.organ_id ,sale.bizdate, sale.member_id, (case when  bu.name is null then '0' when bu.name is not null then bu.name end)  as username,");
        buffer.append(" (case when g.name is not null then g.name when g.name is null then '0' end )  as name,saleEntry.quantity,(saleEntry.quantity*saleEntry.price)as amount ,'billsale' as type");
        buffer.append(" from bill_sale sale");
        buffer.append(" left join bill_sale_entry  saleEntry on sale.id=saleEntry.owner_id");
        buffer.append(" left join bsf_users bu on bu.id=sale.member_id");
        buffer.append(" left join goods g on g.id= saleEntry.goods_id");
        buffer.append(" union all");
        buffer.append(" select charge.id, charge.organ_id,charge.bizdate,charge.member_id ,(case when  bu.name is null then '0' when bu.name is not null then bu.name end)  as username,");
        buffer.append(" gp.name ,chargeEntry.quantity,(chargeEntry.quantity*chargeEntry.price) as amount,'billcharge' as type");
        buffer.append(" from bill_charge charge");
        buffer.append(" left join bill_charge_entry  chargeEntry on charge.id=chargeEntry.owner_id");
        buffer.append(" left join goods_package gp on gp.id=chargeEntry.goodspackage_id");
        buffer.append(" left join bsf_users bu on bu.id=charge.member_id)");
        buffer.append("  as top");
        buffer.append(" where  top.member_id= '" + memberId + "' ");
        //取值跨度(时间)
        if (dateType.equals("year")) {
            buffer.append(" and to_char(top.bizdate,'yyyy')='" + bizDate.substring(0, 4) + "'  ");
        } else if (dateType.equals("month")) {
            buffer.append(" and  to_char(top.bizdate,'yyyy-MM')='" + bizDate.substring(0, 7) + "'  ");
        } else if (dateType.equals("day")) {
            buffer.append(" and  to_char(top.bizdate,'yyyy-MM-dd')='" + bizDate.substring(0, 10) + "'  ");
        }
        buffer.append(" group by top.id,top.organ_id,top.member_id,top.username,top.amount,top.quantity");

        if (returnType.equals("size")) {
            return findBySQL(buffer.toString());
        }
        return this.findBySQL(buffer.toString(), page, rows);
    }

    /**
     * 查询顾客消费活跃度
     *
     * @param dateType
     * @param bizDate
     * @param memberId
     * @param page
     * @param rows
     * @param returnType
     * @return
     */
    public List<Map<String, Object>> getShopTimes(String organId, String dateType, String bizDate, String memberId, Integer page, Integer rows, String returnType) {
        StringBuffer buffer = new StringBuffer();

        buffer.append(" select shop.id,max(shop.bizdate) as lastTime,shop.member_id,shop.memberType,shop.username, sum(chargetimes) as chargetimes,sum(saletimes) as saletimes from");
        buffer.append(" (select sale.id, sale.bizdate,  sale.member_id,(case when member_id is not null then '会员' when member_id is null then '非会员' end) as memberType , (case when  bu.name is null then '0' when bu.name is not null then bu.name end)  as username,");
        buffer.append(" '0' as chargeTimes  ,saleEntry.quantity  as saleTimes");
        buffer.append(" from bill_sale sale");
        buffer.append(" left join bill_sale_entry  saleEntry on sale.id=saleEntry.owner_id");
        buffer.append(" left join bsf_users bu on bu.id=sale.member_id");
        buffer.append(" left join goods g on g.id= saleEntry.goods_id");
        buffer.append(" union all");
        buffer.append(" select charge.id , charge.bizdate,charge.member_id,(case when member_id is not null then '会员' when member_id is null then '非会员' end) as memberType  ,(case when  bu.name is null then '0' when bu.name is not null then bu.name end)  as username,");
        buffer.append(" chargeEntry.quantity as chargeTimes, '0' as saleTimes");
        buffer.append(" from bill_charge charge");
        buffer.append(" left join bill_charge_entry  chargeEntry on charge.id=chargeEntry.owner_id");
        buffer.append(" left join goods_package gp on gp.id=chargeEntry.goodspackage_id");
        buffer.append(" left join bsf_users bu on bu.id=charge.member_id)");
        buffer.append(" as shop");

        buffer.append(" where  shop.member_id= '" + memberId + "' ");
        buffer.append(" and shop.organ_Id= '" + organId + "' ");
        //取值跨度(时间)
        if (dateType.equals("year")) {
            buffer.append(" and to_char(shop.bizdate,'yyyy')='" + bizDate.substring(0, 4) + "'  ");
        } else if (dateType.equals("month")) {
            buffer.append(" and  to_char(shop.bizdate,'yyyy-MM')='" + bizDate.substring(0, 7) + "'  ");
        } else if (dateType.equals("day")) {
            buffer.append(" and  to_char(shop.bizdate,'yyyy-MM-dd')='" + bizDate.substring(0, 10) + "'  ");
        }
        buffer.append(" group by shop.id,shop.member_id,shop.username,shop.memberType");

        if (returnType.equals("size")) {
            return findBySQL(buffer.toString());
        }
        return this.findBySQL(buffer.toString(), page, rows);
    }

    /**
     * 查询会员剩余总额
     *
     * @param organId
     * @param memberId
     * @return
     */
    public Map<String, Object> getMemberBalance(String organId, String memberId) {

        String sql = " select gp.member_id as member_id,bu.name as member_name,ba.file_name as picture,bu.remark as remark,sum(gp.amount) as amount,sum(gp.quantity*goods.price) AS real_price "
                + " from goods_paid gp left join bsf_users bu on bu.id=gp.member_id left join bsf_attachment ba on ba.id=bu.portrait_id LEFT JOIN goods ON gp.goods_id = goods.id "
                + " where gp.organ_id='" + organId + "' and gp.member_id='" + memberId + "' group by gp.member_id,bu.name,ba.file_name,bu.remark ";

        List<Map<String, Object>> bySQL = this.findBySQL(sql);
        if (bySQL != null && bySQL.size() == 1) {
            return bySQL.get(0);
        }
        return null;
    }

}













